package com.supermap.dataservice.cloud.warnInfo;

import com.supermap.dubbo.api.cloud.warnInfo.WarnInfo;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description
 * @date 2018年12月03 下午 4:42
 * @email 1301584033@qq.com
 */
@Mapper
public interface WarningInfoMapper {
    /**
     *多条入库
     * */
    @InsertProvider(type = WarningInfoMapperSQL.class,method = "insertWarnInfoListSQL")
    int insertWarnInfoList(@Param("warnInfoList")List<WarnInfo> warnInfoList );
    /**
     *根据预警ID查询出一条信息
     * */
    @Select("select t.id,t.sender,t.headline,t.description,t.eventTypeCN,t.msgType, t.expires,w.iconurl,w.standard,w.measures from t_warning_info t " +
            " left join cagmpisards.t_warning_iconurl w on t.severity = w.severity and t.eventType = w.eventType  " +
            "  where t.id=#{warnInfoId}")
    WarnInfo getWarnInfoById(@Param("warnInfoId") Integer warnInfoId);

    @SelectProvider(type = WarningInfoMapperSQL.class,method = "getPageWarnInfoByTimeOrAreaCodeSQL")
    List<Map<String,Object>>  getPageWarnInfoByTimeOrAreaCode(@Param("areaCode") String areaCode,@Param("aging") String aging, @Param("beginTime")String beginTime, @Param("endTime")String endTime, @Param("eType")String eType, @Param("pageNum")Integer pageNum, @Param("pageSize")Integer pageSize);

    @SelectProvider(type = WarningInfoMapperSQL.class,method = "getPageWarnInfoByTimeOrAreaCodeCountNumSQL")
    Integer  getPageWarnInfoByTimeOrAreaCodeCountNum(@Param("areaCode") String areaCode,@Param("aging") String aging, @Param("beginTime")String beginTime, @Param("endTime")String endTime, @Param("eType")String eType, @Param("pageNum")Integer pageNum, @Param("pageSize")Integer pageSize);

    /* 获取指定区域、时间范围内的预警类型 */
    @SelectProvider(type =WarningInfoMapperSQL.class,method = "getWarnTypeByTimeOrAreaCodeSQL")
    List<Map<String,Object>> getWarnTypeByTimeOrAreaCode(@Param("areaCode") String areaCode, @Param("beginTime")String beginTime, @Param("endTime")String endTime);

    class WarningInfoMapperSQL{
        public String getPageWarnInfoByTimeOrAreaCodeSQL(Map<String,Object> map){
            StringBuilder stringBuilder=new StringBuilder();
            String areaCode=(String)map.get("areaCode");
            String aging =(String)map.get("aging");
            String beginTime=(String)map.get("beginTime");
            String endTime=(String)map.get("endTime");
            String eType=(String)map.get("eType");
            Integer pageNum=(Integer)map.get("pageNum");
            Integer pageSize=(Integer)map.get("pageSize");
            stringBuilder.append(" select t.id,t.sender,t.headline,t.description,t.eventTypeCN,t.msgType, t.expires,t.effective,w.iconurl,w.standard,w.measures,a.longitude lon,a.latitude lat  from cagmpisards.t_warning_info t " );
            stringBuilder.append(" inner join cagmpisards.t_area a on t.areacode = a.areacode ");
            stringBuilder.append(" left join cagmpisards.t_warning_iconurl w on t.severity = w.severity and t.eventType = w.eventType where 1=1 ");
            if(areaCode!=null && !"86".equals(areaCode)){
                stringBuilder.append(" and  t.areaCode like concat("+areaCode+",'%') ");
            }
            if(eType!= null){
                stringBuilder.append(" and t.eventType = '"+eType+"' ");
            }

            if(aging !=null)
            {
                stringBuilder.append(" and t.expires > date_format(date_add(now(), interval "+ aging+ " hour) ,'%Y-%m-%d %H:%i:%S') ");
            } else if(beginTime!=null&&endTime!=null){
                stringBuilder.append("  and (t.effective between '"+beginTime+"' and '"+endTime+" 23:59:59' ");
                stringBuilder.append("  or t.expires between '"+beginTime+"' and '"+endTime+" 23:59:59') ");
            }

            stringBuilder.append("order by t.effective desc limit "+(pageNum-1)*pageSize  +","+pageSize+"");
            return  stringBuilder.toString();
        }
        public String getPageWarnInfoByTimeOrAreaCodeCountNumSQL(Map<String,Object> map){
            StringBuilder stringBuilder=new StringBuilder();
            String areaCode=(String)map.get("areaCode");
            String aging =(String)map.get("aging");
            String beginTime=(String)map.get("beginTime");
            String endTime=(String)map.get("endTime");
            String eType=(String)map.get("eType");
            stringBuilder.append("select count(1) from t_warning_info where 1=1 ");
            if(areaCode!=null){
                stringBuilder.append(" and areaCode like concat("+areaCode+",'%') ");
            }
            if(eType!=null){
                stringBuilder.append(" and eventType = '"+eType+"' ");
            }
            if(aging !=null)
            {
                stringBuilder.append(" and expires > date_format(date_add(now(), interval "+ aging+ " hour) ,'%Y-%m-%d %H:%i:%S') ");
            } else if(beginTime!=null&&endTime!=null){
                stringBuilder.append("  and (effective between '"+beginTime+"' and '"+endTime+" 23:59:59' ");
                stringBuilder.append("  or expires between '"+beginTime+"' and '"+endTime+" 23:59:59') ");
            }
            return  stringBuilder.toString();
        }
        public String getWarnTypeByTimeOrAreaCodeSQL(Map<String,Object> map){
            StringBuilder stringBuilder=new StringBuilder();
            String areaCode=(String)map.get("areaCode");
            String beginTime=(String)map.get("beginTime");
            String endTime=(String)map.get("endTime");
            stringBuilder.append(" select t.eventTypeCN,t.eventType,count(t.id) num from t_warning_info t where 1=1 " );
            if(areaCode!=null && !"86".equals(areaCode)){
                stringBuilder.append(" and  t.areaCode like concat("+areaCode+",'%') ");
            }
            if(beginTime!=null&&endTime!=null){
                stringBuilder.append("  and t.effective between '"+beginTime+"' and '"+endTime+" 23:59:59' ");
            }
            stringBuilder.append(" group by t.eventType order by group_concat(t.effective order by effective desc) desc ");

            return  stringBuilder.toString();
        }

        /**
         *多条入库
         * */
        public String insertWarnInfoListSQL(Map<String,Object> map){
            List<WarnInfo> warnInfoList=(List<WarnInfo>)map.get("warnInfoList");
            StringBuilder stringBuilder=new StringBuilder();
            stringBuilder.append(" insert  IGNORE  into t_warning_info(id,severity,sender,referencesInfo,note,msgType,identifier,headline,expires,eventTypeCN,eventType,effective,description,areaCode) ");
            stringBuilder.append(" values ");
            if(warnInfoList!=null&&warnInfoList.size()>0){
                for (WarnInfo warnInfo:warnInfoList){
                    stringBuilder.append("(");
                    stringBuilder.append("'"+warnInfo.getId()+"',");
                    stringBuilder.append("'"+warnInfo.getSeverity()+"',");
                    stringBuilder.append("'"+warnInfo.getSender()+"',");
                    stringBuilder.append("'"+warnInfo.getReferencesInfo()+"',");
                    stringBuilder.append("'"+warnInfo.getNote()+"',");
                    stringBuilder.append("'"+warnInfo.getMsgType()+"',");
                    stringBuilder.append("'"+warnInfo.getIdentifier()+"',");
                    stringBuilder.append("'"+warnInfo.getHeadline()+"',");
                    stringBuilder.append("'"+warnInfo.getExpires()+"',");
                    stringBuilder.append("'"+warnInfo.getEventTypeCN()+"',");
                    stringBuilder.append("'"+warnInfo.getEventType()+"',");
                    stringBuilder.append("'"+warnInfo.getEffective()+"',");
                    stringBuilder.append("'"+warnInfo.getDescription()+"',");
                    stringBuilder.append("'"+warnInfo.getAreaCode()+"'");
                    stringBuilder.append("),");
                }
            }
            return stringBuilder.substring(0,stringBuilder.lastIndexOf(",")).toString();

        }
    }
    @Select("" +
            "SELECT t1.id, t1.severity,t1.sender, t1.msgType, t1.headline,t1.expires, \n" +
            "t1.eventTypeCN, t1.effective, t1.description,t1.areaCode, a.longitude, \n" +
            "a.latitude, t1.iconurl\n" +
            "FROM (\n" +
            "SELECT t.id, t.severity,t.sender, t.msgType, t.headline,t.expires,\n" +
            " t.eventTypeCN, t.effective, t.description,t.areaCode, w.iconurl\n" +
            "FROM t_warning_info t left join  t_warning_iconurl w on t.severity = w.severity and t.eventType =w.eventType\n" +
            "WHERE  \n" +
            "t.areaCode LIKE concat(left(#{areaCode},2) , '%') AND t.msgType != \"Cancel\" AND t.expires > NOW()) t1\n" +
            "INNER JOIN t_area a ON t1.areaCode = a.areacode\n" +
            "ORDER BY t1.expires DESC")
    public List<Map<String , Object>> selectValidListByCode(@Param("areaCode") String areaCode);
}
